package Shopfashion;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class ConnectDB {
	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;


	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}


	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/shopfashion?characterEncoding=UTF-8&useUnicode=true";
			// url_sql = "....";
			try {
				this.connect = DriverManager
						.getConnection(url, "root", "123456");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}


	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}


	// thuc hien cac cau lenh cho data sanpham
	
	public ArrayList<SanPham> getAllProducts() throws Exception {
		ArrayList<SanPham> lst = new ArrayList<SanPham>();
		String strSQL = "select * from sanpham";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				String model = rs.getString("model");
				String mausac = rs.getString("mausac");
				String size = rs.getString("size");
				String chatlieu = rs.getString("chatlieu");
				double dongia = Double.parseDouble(rs.getString("dongia"));
				double vongnguc = Double.parseDouble(rs.getString("vongnguc"));
				double vongeo = Double.parseDouble(rs.getString("vongeo"));
				double vongmong = Double.parseDouble(rs.getString("vongmong"));
				String hinhanh = rs.getString("hinhanh");
				String ngaynhap = rs.getString("ngaynhap");
				String mota = rs.getString("mota");
				SanPham sp = new SanPham(model, mausac, size, chatlieu, dongia,
						vongnguc, vongeo, vongmong, hinhanh, ngaynhap, mota);
				lst.add(sp);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}

	public boolean insertNew(SanPham sp) throws Exception {
		String sql = "insert into sanpham values(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, sp.getModel());
		pst.setString(2, sp.getMausac());
		pst.setString(3, sp.getSize());
		pst.setString(4, sp.getChatlieu());
		pst.setDouble(5, sp.getDongia());
		pst.setDouble(6, sp.getVongnguc());
		pst.setDouble(7, sp.getVongeo());
		pst.setDouble(8, sp.getVongmong());
		pst.setString(9, sp.getHinhanh());
		pst.setString(10, sp.getNgaynhap());
		pst.setString(11, sp.getMota());
		return pst.executeUpdate() > 0;

	}

	public boolean deleteProduct(String model) throws Exception {
		String sql = "delete from sanpham where model=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, model);
		return pst.executeUpdate() > 0;
	}


	public int executeUpdate(String strSQL) throws Exception {
		int result = 0;
		try {
			result = getStatement().executeUpdate(strSQL);
		} catch (Exception ex) {
			throw new Exception(ex.getMessage() + " Error at: " + strSQL);
		} finally {
			this.closeConnet();
		}
		return result;
	}

	public SanPham getProductByID(String model) throws Exception {
		String sql = "select * from sanpham where model=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, model);
		ResultSet rs = pst.executeQuery();
		SanPham sp = null;
		if (rs.next()) {
			String mausac = rs.getString("mausac");
			String size = rs.getString("size");
			String chatlieu = rs.getString("chatlieu");
			double dongia = Double.parseDouble(rs.getString("dongia"));
			double vongnguc = Double.parseDouble(rs.getString("vongnguc"));
			double vongeo = Double.parseDouble(rs.getString("vongeo"));
			double vongmong = Double.parseDouble(rs.getString("vongmong"));;
			String hinhanh = rs.getString("hinhanh");
			String ngaynhap = rs.getString("ngaynhap");
			String mota = rs.getString("mota");
			sp = new SanPham(model, mausac, size, chatlieu, dongia,
					vongnguc, vongeo, vongmong, hinhanh, ngaynhap, mota);
		}
		return sp;
	}

	public boolean UpdateProduct(String model, SanPham sp) throws Exception {
		String sql = "update sanpham set mausac=?, size=?, chatlieu=?, dongia=?, vongnguc=?, vongeo=?, "
				+ "vongmong=?, hinhanh=?, ngaynhap=?, mota=? where model=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, sp.getMausac());
		pst.setString(2, sp.getSize());
		pst.setString(3, sp.getChatlieu());
		pst.setDouble(4, sp.getDongia());
		pst.setDouble(5, sp.getVongnguc());
		pst.setDouble(6, sp.getVongeo());
		pst.setDouble(7, sp.getVongmong());;
		pst.setString(8, sp.getHinhanh());
		pst.setString(9, sp.getNgaynhap());
		pst.setString(10, sp.getMota());
		pst.setString(11, sp.getModel());
		return pst.executeUpdate() > 0;
	}

	public void closeConnet() throws SQLException {
		if (rs != null && !rs.isClosed())
			rs.close();
		if (stmt != null)
			stmt.close();
		if (connect != null)
			connect.close();
	}


}

